package cn.xiaobai;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.nio.charset.StandardCharsets;
import java.sql.*;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;

/**
 * sqlite 原生使用
 *
 * @Author yangdaji
 * @Date 2024/2/17
 * @PackageName cn.xiaobai
 * @ClassName Test
 */
public class SqliteCommandTest {
    public static void main(String[] args) throws Exception {

        // 单个操作, 创建, 插入, 查询
        createUser();
        insertUser();
        queryUser();

        // 执行脚本 sql
        execScriptSql();
        queryUser();
    }

    /**
     * 获取连接
     *
     * @return
     */
    public static Connection conn() {

        // 设置本地路径的 test 数据库, 格式: jdbc:sqlite:your-path
        String url = "jdbc:sqlite:C:/Users/dell/Desktop/test/sqllite/sqllite/test.db";
        Connection conn = null;
        try {
            // 加载 sqlite 的 JDBC 驱动, 实际上这一步可以省略, 会自动加载
            Class.forName("org.sqlite.JDBC");

            // 获取连接
            conn = DriverManager.getConnection(url);
//            System.out.println("连接成功!");
            return conn;
        } catch (SQLException | ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 关闭连接
     *
     * @param conn
     */
    public static void close(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


    /**
     * 创建 user 表
     */
    public static void createUser() {
        String createUser = "create table if not exists user(\n" +
                "  id integer primary key autoincrement,\n" +
                "  name varchar\n" +
                ");";
        // 查看一下是否存在创建的 user 表
        showTables();
        Connection conn = conn();
        try {
            Statement statement = conn.createStatement();
            conn.setAutoCommit(false);
            // 执行创建表 sql
            statement.execute(createUser);
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                e.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            close(conn);
        }
        showTables();
    }

    /**
     * 插入数据到 user 表
     */
    public static void insertUser() {
        String insertUser = "insert into user(name) values\n" +
                "('小米'),\n" +
                "('华为');";
        Connection conn = conn();
        try {
            Statement statement = conn.createStatement();
            conn.setAutoCommit(false);
            // 执行插入user表数据
            statement.execute(insertUser);
            conn.commit();
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                e.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            close(conn);
        }
    }

    /**
     * 查询 user 表
     */
    public static void queryUser() {
        String queryUser = "select * from user;";

        Connection conn = conn();
        try {
            Statement statement = conn.createStatement();
            // 执行查询 sql
            ResultSet resultSet = statement.executeQuery(queryUser);
            System.out.println("id\t|\tname");
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                System.out.println(id + "\t|\t" + name);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn);
        }
    }


    /**
     * 查询所有表
     */
    public static void showTables() {
        Connection conn = conn();
        try {
            DatabaseMetaData metaData = conn.getMetaData();
            ResultSet resultSet = metaData.getTables(null, null, "%", null);
            List<String> tables = new ArrayList<>();
            while (resultSet.next()) {
                tables.add(resultSet.getString("table_name"));
            }
            close(conn);
            System.out.println("查询所有表: \n" + Arrays.toString(tables.toArray()));
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }

    }


    /**
     * 执行 sql 脚本
     */
    public static void execScriptSql() {
        List<String> schemeSqlList = getSchemeSql();
        Connection conn = conn();
        try {
            Statement statement = conn.createStatement();
            conn.setAutoCommit(false);
            for (String sql : schemeSqlList) {
                statement.executeUpdate(sql);
                conn.commit();
            }
        } catch (SQLException e) {
            try {
                conn.rollback();
            } catch (SQLException ex) {
                e.printStackTrace();
            }
            e.printStackTrace();
        } finally {
            close(conn);
        }
    }

    /**
     * 获取 sql 脚本
     *
     * @return
     */
    public static List<String> getSchemeSql() {
        // 脚本文件替换为自己本地的路径
        String scheme = "C:/Users/dell/Desktop/test/sqllite/sqllite/scheme.sql";
        try {
            InputStreamReader reader = new InputStreamReader(new FileInputStream(scheme), StandardCharsets.UTF_8);
            BufferedReader bufferedReader = new BufferedReader(reader);

            StringBuilder schemeSql = new StringBuilder();
            String line;
            while ((line = bufferedReader.readLine()) != null) {
                schemeSql.append(line).append("\n");
            }

            String[] split = schemeSql.toString().split(";");
            return new ArrayList<>(Arrays.asList(split));
        } catch (IOException e) {
            e.printStackTrace();
        }

        return new ArrayList<>(0);
    }

}
